﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MolyMart
{
    class SqliteDb
    {
        private string _dbPath;
        private string _dbstring;
        private SQLiteConnection _sqlConnection;
        private List<ServerValuesItem> _valueItems = new List<ServerValuesItem>();
        public int itemCount = 0;

        public SqliteDb(string dbPath = "Datebase.db")
        {
            _dbPath = dbPath;
            _dbstring = "Data Source = " + _dbPath;
        }

        public void CreateDb()
        {
            File.Delete(_dbPath);
            _sqlConnection = new SQLiteConnection(_dbstring);
            _sqlConnection.Open();
        }

        public void CreateTable()
        {
            SQLiteCommand command = new SQLiteCommand(CreateCncStateValuesTableString(), _sqlConnection);
            command.ExecuteNonQuery();
        }

        public void InsertItem(ServerValuesItem item)
        {
            _valueItems.Add(item);
            itemCount = _valueItems.Count;
        }

        public void CommitItems()
        {
                using (SQLiteTransaction transaction = _sqlConnection.BeginTransaction())
                {
                    foreach (ServerValuesItem item in _valueItems)
                    {
                        SQLiteCommand command = new SQLiteCommand(_sqlConnection)
                        {
                            Transaction = transaction,
                            CommandText = "INSERT INTO CNC_States VALUES (@id,@ServerId,@Data)"
                        };
                        command.Parameters.AddRange(new SQLiteParameter[]
                        {
                            new SQLiteParameter("@id",Utils.GetUnixTimeStamp()), 
                            new SQLiteParameter("@ServerId",item.ServerId),
                            new SQLiteParameter("@Data",CreateServerValuesItemString(item)),  
                            
                        });
                        command.ExecuteNonQuery();
                    }
                    _valueItems.Clear();
                    transaction.Commit();
                }
        }

        private string CreateCncStateValuesTableString()
        {
                return @"CREATE TABLE IF NOT EXISTS CNC_States(id text, ServerId text, DataArea text);";
        }

        private string CreateServerValuesItemString(ServerValuesItem item)
        {
            string[] values = item.Values.Select(CreateValuesItemString).ToArray();
            return @"[" + string.Join(@",", values) + @"]";      
        }

        private string CreateValuesItemString(ValuesItem item)
        {
            return $"{"{"}Name:{item.Name},KeyID:{item.KeyId},Value:{item.Value},Quality:{item.Quality},TimeStamp:{item.TimeStamp},Format:{item.Format}{"}"}";
        }
    }
}
